package com.dhcc.DRGsHosp.application.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.dayatang.querychannel.QueryChannelService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.dhcc.DRGsHosp.application.DepartmentApplication;
import com.dhcc.DRGsHosp.core.domain.Department;
import com.dhcc.DRGsHosp.core.domain.IndexCal;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;

/**
 * 
 * @ClassName: DepartmentApplicationImpl  
 * @Description ：科室信息管理   
 * @Date: 2017年6月9日 下午9:51:54  
 * @Author LiuHongJun 
 * @Version 2.5.0 DHC-DRGs
 */
@Named("departmentApplicationImplqiaoyilong20181026")
@Transactional
public class DepartmentApplicationImpl implements DepartmentApplication {
	private static final Logger LOGGER = LoggerFactory.getLogger(DepartmentApplicationImpl.class);
	
	private QueryChannelService queryChannel;
	
	private EntityRepository repository ;
	
    @Autowired  
    private JdbcTemplateObj jdbcTemplateObj; 
    
    @Autowired
	private NamedParameterJdbcTemplate jdbcTempSeverice;
	
	private EntityRepository getEntityRepository() {
		if(repository == null ){
			repository = InstanceFactory.getInstance(EntityRepository.class, "repository" ) ;
		}
		return repository;
	}
	
    private QueryChannelService getQueryChannelService(){
       if(queryChannel==null){
          queryChannel = InstanceFactory.getInstance(QueryChannelService.class,"queryChannel");
       }
     return queryChannel;
    }

	public Department getDepartment(String depCode) {
		if(!StringUtils.hasLength(depCode)) return null;
		return Department.get(Department.class, depCode);
	}
	
	public void creatDepartment(Department department) {
		department.save();
	}
	
	public void updateDepartment(Department department) {
//		Department.update(department);
		department.save();
	}
	
	public void removeDepartment(Department department) {
		if(department != null){
			department.remove();
		}
	}
	
	public void removeDepartments(Set<Department> departments) {
		for (Department department : departments) {
			department.remove();
		}
	}
	/**
	 *  @Description：获取科室信息
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.DepartmentApplication#findAllDepartment()
	 */
	public List<Department> findAllDepartment() {
		return Department.findAll(Department.class);
	}
	/**
	 * 
	 *  @Description：获取科室信息
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.DepartmentApplication#findDepsByDepGroup(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	public List<Map<String,Object>> findDepsByDepGroup(IndexCal queryVo){
		String deps = queryVo.getDeps();//获取多个科室
		String dateFrom=DrgsIndex.formateDateFrom(queryVo.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(queryVo.getDateTo());
		String hospCode=queryVo.getHospCode();
		StringBuilder sql = null;
		sql = new StringBuilder(" select dep.dep_code depCode,dep.dep_name depDesc,dep.dep_order DEPORDER from DRGs.t_Department dep where exists ");
		sql.append(" (select 1 from DRGs.MR_IPINFO med left join drgs.mr_baseinfo mrb on med.pmainindex=mrb.pmainindex");
		sql.append(" where dep.dep_code = med.P00300001 ");
		if(StringUtils.hasLength(hospCode)){
			sql.append(" and instr('"+hospCode+"',mrb.p00010001)>0 ");
		}
		sql.append(" and to_char(med.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and  '"+dateTo+"' ");
		sql.append(" ) ");
		if(queryVo.getPardepgroupCode() != null && !"".equals(queryVo.getPardepgroupCode())){
			sql.append(" and dep.pardepgroup_code='"+queryVo.getPardepgroupCode()+"' ");
		}
		if(StringUtils.hasLength(deps))
		{
			deps = "'" + deps.replaceAll(",","','") + "'";
			sql.append(" and dep.dep_code in ("+deps+") ");
		}
		sql.append(" order by dep.dep_order ");
		return jdbcTemplateObj.queryForList(sql.toString());
	}

	/**
	 * 
	 * @methodname    :getAllDeps
	 * @description   : 
	 * @param         :
	 * @author        :fulianxia
	 * @return        :
	 * @date          :2015年5月8日
	 */
	@Override
	public List<Department> getAllDeps(){
		StringBuilder jpql = new StringBuilder("select new  Department(department.depCode,department.depName) from Department department");
		List<Department> departments = getQueryChannelService()
				   .createJpqlQuery(jpql.toString())
				   .list();
		return departments;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.DepartmentApplication#findAllDepartment(com.dhcc.DRGsHosp.core.domain.Department)
	 */
	@Override
	public List<Department> findAllDepartment(Department entity) {
		String depGroupCode = entity.getParDepGroupCode();
		String depCode = entity.getDepCode();
		StringBuilder jpql = new StringBuilder("select new  Department(department.depCode,department.depName) from Department department where 1=1 ");
		if (StringUtils.hasLength(depGroupCode)) {
			//科室组代码
			jpql.append(" and department.parDepGroupCode='"+depGroupCode+"'");
		}
		if (StringUtils.hasLength(depCode)) {
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				jpql.append(" and department.depCode in ("+depCode+")");
			}
			else
			{//一个科室
				jpql.append(" and department.depCode = '"+depCode+"'");
			}
		}
		jpql.append(" order by  to_number(department.depOrder)");
		List<Department> departments = getQueryChannelService()
				   .createJpqlQuery(jpql.toString())
				   .list();
		return departments;
	}
	
	/**
	 * 
	 * @methodname    :findDepCodeByName
	 * @description   :根据科室名称查询科室代码
	 * @param         :
	 * @author        :liqingyang
	 * @return        :
	 * @date          :2015年5月19日
	 */
	public String findDepCodeByName(String depName){
		return Department.findDepCodeByName(depName);
	}
	

	@Override
	public String findDepNameByCode(String depCode) {
		return Department.findDepNameByCode(depCode);
	}

	@Override
	public List<Object> findDepFromMedicalrecord(IndexCal IndexCal) {
		String dateFrom=DrgsIndex.formateDateFrom(IndexCal.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(IndexCal.getDateTo());
		String sql = "select t.p00300001"
				+ " from drgs.mr_ipinfo t"
				+ " where  t.out_date between to_date('"+dateFrom+"','yyyy-mm-dd hh24:mi:ss') and to_date('"+dateTo+"','yyyy-mm-dd hh24:mi:ss')  ";
		sql+= " and t.out_dep_code is not null"
				+ " group by out_dep_code ";
		return getQueryChannelService().createSqlQuery(sql).list();
	}

	@Override
	public String findDepNamesByDepGroupCode(String depgroupCode) {
		StringBuilder jpql = new StringBuilder("select department.depName from Department department where department.parDepGroupCode = '"+depgroupCode+"'");
		List<String> depName = getQueryChannelService()
			   .createJpqlQuery(jpql.toString())
			   .list();
		String names = "";
		int l=depName.size();
		for(int i =0;i<l;i++)
		{
			if(0==i)
			{names = depName.get(i);}
			else
			{names = names+","+depName.get(i);}
		}
		return names;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.DepartmentApplication#isOrderExist(java.lang.String)
	 */
	@Override
	public Boolean isOrderExist(String order,String code) {
		String jpql = "select department from Department department where department.depOrder="+order;
		if(""!=code && null!=code){
			jpql += " and department.depCode <> '"+code+"' ";
		}
		List<Department> departments = getQueryChannelService().createJpqlQuery(jpql).list();
		if(departments.size() > 0){
			return true;
		}
		return false;
	}

	@Override
	public List<Department> getDepartmentByDepGroup(String depGroupCode) {
		String jpql = "select department from Department department where department.parDepGroupCode='"+depGroupCode+"'";
		List<Department> departments = getQueryChannelService().createJpqlQuery(jpql).list();
		return departments;
	}

	@Override
	public List<Object[]> findWardsByCondition(IndexCal IndexCal) {
		String depGroupCode =  IndexCal.getPardepgroupCode();//科室组代码
		String depCode = IndexCal.getPardepCode();//科室代码 
		String dateFrom=DrgsIndex.formateDateFrom(IndexCal.getDateFrom());
		String dateTo=DrgsIndex.formateDateTo(IndexCal.getDateTo());
		StringBuilder sql = new StringBuilder();
		sql.append("  select distinct(ipinfo.P00310001),ipinfo.P00310000 out_ward from drgs.mr_ipinfo ipinfo left join drgs.t_department dep  \n ");
		sql.append(" on ipinfo.P00300001= dep.dep_code where to_char(ipinfo.P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' ");
		if(StringUtils.hasLength(depGroupCode)){
			sql.append(" and dep.pardepgroup_code = '"+depGroupCode+"' \n ");
		}
		if(StringUtils.hasLength(depCode)){
			sql.append(" and ipinfo.p00300001 =  '"+depCode+"' \n ");
		}
		sql.append(" order by ipinfo.p00310001 \n ");
		List<Object[]> mrList = getQueryChannelService().createSqlQuery(sql.toString()).list();
		return mrList;
	}

	@Override
	public int excuteUpdate(String sql, Map<String, Object> condition) {
		// TODO Auto-generated method stub
		return getEntityRepository().createSqlQuery(sql).setParameters(condition).executeUpdate();
	}
/**
 * 
 *  @Description：获取科室理论drg
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.DepartmentApplication#findDepDRGs(java.lang.String)
 */
	@Override
	public List<Object[]> findDepDRGs(String depCode) {
		String sql=" select b.drg_code, b.drg_desc from drgs.t_departmentdrg a \n"
				+ "left join drgs.t_drgs b \n "
				+ "on a.pardrg_code=b.drg_code and b.ver_code=a.version \n "
				+ "where a.pardep_code='"+depCode+"' ";
		return getEntityRepository().createSqlQuery(sql).list();
	}
	
	@Override
	public Department findDepartmentByDepCode(String depCode) {
		// TODO Auto-generated method stub
		String jpql = "select _dep from Department _dep where _dep.depCode = :depCode" ;
		List<Department> listDep = getEntityRepository().createJpqlQuery(jpql).addParameter("depCode", depCode).list() ;
		Department dep = null ;
		if( null != listDep && listDep.size() > 0) {
			dep = listDep.get(0) ;
		}
		return dep;
	}
	
	/**
	 * 
	 *  @Description：
	 *  @Author ChenHanlin  
	 *  @see com.dhcc.DRGsHosp.application.DepartmentApplication#executeQueryForList(java.lang.String, java.lang.Object)
	 */
	public List<Map<String, Object>> executeQueryForList(String sql,Map<String,Object> condition) {
		List<Map<String, Object>> mapLsit = jdbcTempSeverice.queryForList(sql, condition)  ;
		return mapLsit;
	}
/**
 * 
 *  @Description：根据院区编码获取科室信息
 *  @Author lhj 
 *  @see com.dhcc.DRGsHosp.application.DepartmentApplication#findAllDepByHospCode(com.dhcc.DRGsHosp.core.domain.IndexCal)
 */
	@Override
	public List<Map<String, Object>>  findAllDepByHospCode(IndexCal entity) {
		String hospCode=entity.getHospCode();
		String depGroupCode = entity.getPardepgroupCode();
		String depCode = entity.getPardepCode();
		String deps = entity.getDeps();
		String sqlStr=" SELECT  D.DEP_CODE depCode,D.DEP_NAME depName,D.PARDEPGROUP_CODE DEPGROUPCODE,D.parhosp_code hospcode,h.hosp_name hospname FROM  DRGS.T_DEPARTMENT D  \n ";
		sqlStr+=" LEFT JOIN DRGS.T_HOSPINFO H ON D.PARHOSP_CODE=H.HOSP_CODE";	
		sqlStr+=" WHERE D.DEP_CODE IS NOT NULL AND D.Dep_Type='E' ";
			if(StringUtils.hasLength(hospCode)){
				sqlStr+=" and  instr('"+hospCode+"', D.parhosp_code)>0 ";	
			}
			if(StringUtils.hasLength(depGroupCode)){
				sqlStr+=" AND D.PARDEPGROUP_CODE='"+depGroupCode+"'   ";	
			}
			if(StringUtils.hasLength(depCode)){
				sqlStr+=" AND D.DEP_CODE='"+depCode+"' ";	
			}
			if(StringUtils.hasLength(deps)){
				deps = deps.replace(",", "','");
				sqlStr+=" and  D.DEP_CODE IN('"+deps+"') ";	
			}
			sqlStr+=" order by to_number(D.DEP_ORDER) ";
		return jdbcTemplateObj.queryForList(sqlStr);
	}
	
	@Override
	public List<Map<String, Object>> findAllHosp() {
		StringBuffer strb = new StringBuffer() ;
		strb.append(" SELECT HOSP_CODE,HOSP_NAME FROM DRGS.T_HOSPINFO order by to_number(HOSP_ORDER)  ");
		List<Map<String, Object>> mapLsit = jdbcTempSeverice.queryForList(strb.toString(),new HashMap<String,Object>())  ;
		return mapLsit;
	}

	/**  
	 *  @Description：
	 *  @Author mazhaoyan  
	 *  @see com.dhcc.DRGsHosp.application.DepartmentApplication#updateDepNameByCode(com.dhcc.DRGsHosp.core.domain.Department)  
	 */
	@Override
	public int updateDepNameByCode(Department dep) {
		String sql = "update drgs.T_DEPARTMENT dep  set  dep.dep_name = :depName where dep.dep_code = :depCode" ;
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(dep);
		return jdbcTempSeverice.update(sql, paramSource ) ;
	}

	/**  
	 *  @Description：
	 *  @Author mazhaoyan  
	 *  @see com.dhcc.DRGsHosp.application.DepartmentApplication#updateDepCodeInIpinfo(java.lang.String, java.lang.String)  
	 */
	@Override
	public void updateDepCodeInIpinfo(String oldCode, String newCode) {
		String sql =  " update drgs.mr_ipinfo ip set ip.p00300001 = ? where ip.p00300001 = ? \n " ;
		try {
			jdbcTemplateObj.update(sql,new Object[] {newCode,oldCode}) ;
		} catch (Exception e) {
			LOGGER.error(e.getMessage());
		}
	}

	/**  
	 *  @Description：
	 *  @Author mazhaoyan  
	 *  @see com.dhcc.DRGsHosp.application.DepartmentApplication#updateDepCodeInDep(java.lang.String, java.lang.String)  
	 */
	@Override
	public void updateDepCodeInDep(String oldCode, String newCode) {
		String sql = "update drgs.t_department dep set dep.dep_code = ? where dep.dep_code = ? " ;
		try {
			jdbcTemplateObj.update(sql,new Object[] {newCode,oldCode}) ;
		} catch (Exception e) {
			LOGGER.error(e.getMessage());
		}
	}

	/**  
	 *  @Description：更新科室所属的院区code
	 *  @Author mazhaoyan  
	 *  @see com.dhcc.DRGsHosp.application.DepartmentApplication#updateBaseInfo(java.lang.String, java.lang.String)  
	 */
	@Override
	public void updateBaseInfo(String depCode, String hospCode) {
		String sql = "update drgs.mr_baseinfo a set a.p00010001=? where exists (select 1 from drgs.mr_ipinfo b where a.pmainindex=b.pmainindex and b.P00300001 = ?) " ;
		try {
			jdbcTemplateObj.update(sql,new Object[] {hospCode,depCode}) ;
		} catch (Exception e) {
			LOGGER.error(e.getMessage());
		}
	}

	/**  
	 * @Description: 根据科室编码获取科室组编码
	 * @author shaozhiyuan 
	 * @date 2018年4月4日  
	 * @version V3.1.0
	 */  
	@Override
	public String getDepGroupByDepCode(String code) {
		String sqlStr=" SELECT DT.PARDEPGROUP_CODE CODE FROM DRGS.T_DEPARTMENT DT WHERE DT.DEP_CODE='"+code+"' ";
		List<Map<String, Object>> mapLsit = jdbcTemplateObj.queryForList(sqlStr.toString());
		String depGroupCode="";
		if(mapLsit.size()>0){
			Map<String, Object> map=mapLsit.get(0);
			depGroupCode=map.get("CODE")==null?"":map.get("CODE").toString().trim();
		}
		return depGroupCode;
	}
	
	@Override
	public Double findOrderMax() {
		String sql = "select max(t.dep_order) from drgs.T_DEPARTMENT t" ;
		Double intg = jdbcTemplateObj.queryForObject(sql, Double.class) ;
		if(intg == null) {
			return 0.;
		} else {
			return intg ;
		}
	}
	
	@Override
	public List<Map<String, Object>> findDepNamesByDepCodes(String depCodes){
		String sql = "SELECT DISTINCT CASE WHEN DEP_HIS2 IS NOT NULL THEN DEP_HIS2 ELSE DEP_NAME END DEP_NAME FROM DRGS.T_DEPARTMENT WHERE INSTR('"+depCodes+"', DEP_CODE)>0";
		List<Map<String, Object>> depNameList = jdbcTemplateObj.queryForList(sql);
		return depNameList;
	}
}
